package com.deng.maintenance.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.Collator;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;

import com.deng.common.SQLHelper;
import com.deng.common.dao.impl.OrganizationImpl;
import com.deng.maintenance.dao.IVoteVsOrganization;
import com.deng.maintenance.data.ComparatorFullOrganizationName;
import com.deng.maintenance.vo.VoteVsOrganizationVo;

public class VoteVsOrganizationImpl extends SQLHelper implements
		IVoteVsOrganization {

	public int deleteData(VoteVsOrganizationVo vo) {
		try{
			String sql = "delete vote_vs_organization" +
					" where vote_id = "+vo.vote_id+" and organization_id = "+vo.organization_id;
			Connection conn = getConnect();
			int x = updateData(conn,sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}
	public int deleteDataOperate(VoteVsOrganizationVo vo) {
		try{
			String sql = "delete vote_vs_operate" +
					" where vote_id = "+vo.vote_id+" and operate_id = "+vo.organization_id;
			Connection conn = getConnect();
			int x = updateData(conn,sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}

	public int insertData(VoteVsOrganizationVo vo) {
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn, "select * from vote_vs_organization where vote_id = "+vo.vote_id+
					" and organization_id = "+vo.organization_id);
			if(rs.next()){
				freeConnection(conn);
				return 0;
			}			
			String sql = "insert into vote_vs_organization (vote_id,organization_id,frq_code) " +
					"values("+vo.vote_id+","+vo.organization_id+",'"+vo.frq_code+"')";
			
			int x = updateData(conn,sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}

	public List<VoteVsOrganizationVo> searchAllDataByVoteId(int vote_id) {//******************
		try{
			String sql = "select a.id as organization_id,vote_vs_organization.vote_id," +
					"	vote_vs_organization.frq_code, a.full_name as long_name" +
					"	from dbo.FUN_GetFullNameOrganization(1) as a" +
					"	left join vote_vs_organization" +
					"	on a.id = vote_vs_organization.organization_id" +
					"	and vote_id = " +vote_id+
					"	order by long_name ";
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,sql);
			List<VoteVsOrganizationVo> l = new ArrayList<VoteVsOrganizationVo>();
			while(rs.next()){
				VoteVsOrganizationVo vo = new VoteVsOrganizationVo();
				vo.vote_id = rs.getInt("vote_id");
				vo.organization_id = rs.getInt("organization_id");
				vo.frq_code = rs.getString("frq_code");
				vo.organization_long_name = rs.getString("long_name");
				l.add(vo);				
			}
			freeConnection(conn);
			return l;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}
	
	public List<VoteVsOrganizationVo> searchAllDataByVoteIdAndFrq(int vote_id,String frq_code) {
		try{
			String sql = "select a.id as organization_id,vote_vs_organization.vote_id," +
					" vote_vs_organization.frq_code, a.full_name as long_name" +
					" from" +
					" (" +
//		SQL 2005 is fine			" select id,tp+'-'+name as full_name" +
					" select id,cast(tp as nvarchar)+'-'+cast(name as nvarchar) as full_name" +
					" from(" +
					" select id,name,duty_id," +
					" (select name from DICT_DUTY_TYPE where DICT_DUTY_TYPE.id = t_sys_stat_department.duty_id)  as tp" +
					" from t_sys_stat_department where is_leaf = 1" +
					" ) as k" +
					" ) as a" +
					" left join vote_vs_organization" +
					" on a.id = vote_vs_organization.organization_id" +
					" and vote_id = " +vote_id+
					" and frq_code = '"+frq_code+"'" +
					" order by long_name ";
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,sql);
			List<VoteVsOrganizationVo> l = new ArrayList<VoteVsOrganizationVo>();
			while(rs.next()){
				VoteVsOrganizationVo vo = new VoteVsOrganizationVo();
				vo.vote_id = rs.getInt("vote_id");
				vo.organization_id = rs.getInt("organization_id");
				vo.frq_code = rs.getString("frq_code");
				vo.organization_long_name = rs.getString("long_name");
				l.add(vo);				
			}
			freeConnection(conn);
			return l;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	public List<VoteVsOrganizationVo> searchAllDataByVoteIdAndFrq2(int vote_id,String frq_code) {
		try{
			String sql = "select t_sys_stat_organization_vs_dispense_organization.id as organization_id," +
				"	VOTE_VS_DISPENSE.vote_id,VOTE_VS_DISPENSE.frq_code," +
				"	t_sys_stat_organization_vs_dispense_organization.name " +
				"	from t_sys_stat_organization_vs_dispense_organization " +
				"	left join VOTE_VS_DISPENSE on VOTE_VS_DISPENSE.dispense_id " +
				"	= t_sys_stat_organization_vs_dispense_organization.id " +
				"	and vote_id = "+vote_id+
				"	and frq_code = '"+frq_code+"'"+
				"	order by t_sys_stat_organization_vs_dispense_organization.name"; 
			
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,sql);
			List<VoteVsOrganizationVo> l = new ArrayList<VoteVsOrganizationVo>();
			while(rs.next()){
				VoteVsOrganizationVo vo = new VoteVsOrganizationVo();
				vo.vote_id = rs.getInt("vote_id");
				vo.organization_id = rs.getInt("organization_id");
				vo.frq_code = rs.getString("frq_code");
				vo.organization_long_name = rs.getString("name");
				l.add(vo);				
			}
			freeConnection(conn);
			return l;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}
	
	public VoteVsOrganizationVo searchDataByVoteId(int vote_id) {
		// TODO Auto-generated method stub
		return null;
	}

	public int updateData(VoteVsOrganizationVo vo) {
		// TODO Auto-generated method stub
		return 0;
	}
	public static void main(String s[]){
		List l = new VoteVsOrganizationImpl().searchAllDataByVoteIdAndFrq(1, "0001");
		for(int i =0;i< l.size();i++){
			System.out.println(((VoteVsOrganizationVo)l.get(i)).organization_long_name);
		}
	}

	public List<VoteVsOrganizationVo> searchAllDataByVoteId2(int vote_id) {
		try{
			String sql = "select t_sys_stat_organization_vs_dispense_organization.id as organization_id," +
					"	VOTE_VS_DISPENSE.vote_id,VOTE_VS_DISPENSE.frq_code," +
					"	t_sys_stat_organization_vs_dispense_organization.name " +
					"	from t_sys_stat_organization_vs_dispense_organization " +
					"	left join VOTE_VS_DISPENSE on VOTE_VS_DISPENSE.dispense_id " +
					"	= t_sys_stat_organization_vs_dispense_organization.id " +
					"	and vote_id = "+vote_id+
					"	order by t_sys_stat_organization_vs_dispense_organization.name"; 

			Connection conn = getConnect();
			ResultSet rs = queryData(conn,sql);
			List<VoteVsOrganizationVo> l = new ArrayList<VoteVsOrganizationVo>();
			while(rs.next()){
				VoteVsOrganizationVo vo = new VoteVsOrganizationVo();
				vo.vote_id = rs.getInt("vote_id");
				vo.organization_id = rs.getInt("organization_id");
				vo.frq_code = rs.getString("frq_code");
				vo.organization_long_name = rs.getString("name");
				l.add(vo);				
			}
			freeConnection(conn);
			return l;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	public int deleteData2(VoteVsOrganizationVo vo) {
		try{
			String sql = "delete VOTE_VS_DISPENSE" +
					" where vote_id = "+vo.vote_id+" and dispense_id = "+vo.organization_id;
			Connection conn = getConnect();
			int x = updateData(conn,sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}

	public int insertData2(VoteVsOrganizationVo vo) {
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn, "select * from VOTE_VS_DISPENSE where vote_id = "+vo.vote_id+
					" and dispense_id = "+vo.organization_id);
			if(rs.next()){
				freeConnection(conn);
				return 0;
			}		
			String sql = "insert into VOTE_VS_DISPENSE (vote_id,dispense_id,frq_code) " +
					"values("+vo.vote_id+","+vo.organization_id+",'"+vo.frq_code+"')";
			int x = updateData(conn,sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}

	public List<VoteVsOrganizationVo> searchAllDataByDispenseIdAndFrq2(
			int dispense_id, String frq_code) {

		try{
			String sql = "select * from VOTE_VS_DISPENSE,VOTE_DICT2 " +
					"	where dispense_id = "+dispense_id+
					" 	and frq_code = '"+frq_code+"'" +
					"	and VOTE_VS_DISPENSE.vote_id = VOTE_DICT2.id"; 
			
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,sql);
			List<VoteVsOrganizationVo> l = new ArrayList<VoteVsOrganizationVo>();
			while(rs.next()){
				VoteVsOrganizationVo vo = new VoteVsOrganizationVo();
				vo.vote_id = rs.getInt("vote_id");
				vo.organization_id = rs.getInt("dispense_id");
				vo.frq_code = rs.getString("frq_code");
				vo.vote_name = rs.getString("name");
				l.add(vo);				
			}
			freeConnection(conn);
			return l;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	
	}

	public List<VoteVsOrganizationVo> searchAllDataOperateByVoteIdAndFrq(
			int vote_id, String frq_code) {

		try{
			String sql = "select a.id as organization_id,vote_vs_operate.vote_id," +
					"	vote_vs_operate.frq_code, a.name as long_name" +
					"	from t_sys_stat_operate_department as a" +
					"	left join vote_vs_operate" +
					"	on a.id = vote_vs_operate.operate_id" +
					"	and vote_id = " +vote_id+
					"	and frq_code = '"+ frq_code+"'" +
					"	order by long_name ";
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,sql);
			List<VoteVsOrganizationVo> l = new ArrayList<VoteVsOrganizationVo>();
			while(rs.next()){
				VoteVsOrganizationVo vo = new VoteVsOrganizationVo();
				vo.vote_id = rs.getInt("vote_id");
				vo.organization_id = rs.getInt("organization_id");
				vo.frq_code = rs.getString("frq_code");
				vo.organization_long_name = rs.getString("long_name");
				l.add(vo);				
			}
			freeConnection(conn);
			return l;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	
	}
	public int insertDataOperate(VoteVsOrganizationVo vo) {
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn, "select * from vote_vs_operate where vote_id = "+vo.vote_id+
					" and operate_id = "+vo.organization_id);
			if(rs.next()){
				freeConnection(conn);
				return 0;
			}			
			String sql = "insert into vote_vs_operate (vote_id,operate_id,frq_code) " +
					"values("+vo.vote_id+","+vo.organization_id+",'"+vo.frq_code+"')";
			
			int x = updateData(conn,sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}
	public int deleteDataAdmin(VoteVsOrganizationVo vo) {
		try{
			String sql = "delete VOTE_VS_ADMIN" +
					" where vote_id = "+vo.vote_id+" and admin_id = "+vo.organization_id;
			Connection conn = getConnect();
			int x = updateData(conn,sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}
	public int insertDataAdmin(VoteVsOrganizationVo vo) {
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn, "select * from VOTE_VS_ADMIN where vote_id = "+vo.vote_id+
					" and admin_id = "+vo.organization_id);
			if(rs.next()){
				freeConnection(conn);
				return 0;
			}			
			String sql = "insert into VOTE_VS_ADMIN (vote_id,admin_id,frq_code) " +
					"values("+vo.vote_id+","+vo.organization_id+",'"+vo.frq_code+"')";
			
			int x = updateData(conn,sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}
	public List<VoteVsOrganizationVo> searchAllDataAdminByVoteIdAndFrq(
			int vote_id, String frq_code) {
		try{
			String sql = "select a.id as organization_id,vote_vs_ADMIN.vote_id," +
					"	vote_vs_ADMIN.frq_code, a.name as long_name" +
					"	from t_sys_stat_admin_department as a" +
					"	left join vote_vs_ADMIN" +
					"	on a.id = vote_vs_ADMIN.admin_id" +
					"	and vote_id = " +vote_id+
					"	and frq_code = '"+ frq_code+"'" +
					"	order by long_name ";
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,sql);
			List<VoteVsOrganizationVo> l = new ArrayList<VoteVsOrganizationVo>();
			while(rs.next()){
				VoteVsOrganizationVo vo = new VoteVsOrganizationVo();
				vo.vote_id = rs.getInt("vote_id");
				vo.organization_id = rs.getInt("organization_id");
				vo.frq_code = rs.getString("frq_code");
				vo.organization_long_name = rs.getString("long_name");
				l.add(vo);				
			}
			freeConnection(conn);
			return l;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	
	}
}
